What is a Trigger? What are the Types Of Triggers? How to Display Information About Events - Part One

Introduction

 

While working with any technology, we need to study its important aspects and features. When we talk about Database Technology, then we should very clear about the basics of the database to learn advanced features such as a stored procedure, functions, views, packages, sequence, synonyms and triggers. In this article, I will explain what is a trigger and the types of triggers.

 

Let’s begin...

 

What is a trigger?

 

A trigger is a special type of stored procedure in database that automatically invokes/runs/fires when an event occurs in the database server. A trigger uses the special table to keep a copy of the row which we have just inserted, deleted or modified.

 

What are the types of triggers?

 

There are three types of triggers in SQL Server.  

1. DDL Trigger
2. DML Trigger
3. Logon Trigger

DDL Trigger

 

DDL trigger runs when DDL events occur in the database. DDL events are CREATE, ALTER and DROP statements.

 

The DDL triggers are useful in the following cases:

• Record changes in the database schema
• Prevent some specific changes to the database schema
• Respond to a change in the database schema

To create a DDL trigger, use the following syntax

CREATE TRIGGER <Trigger_Name>
ON DATABASE
FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
BEGIN
<Trigger Body>
END

We will see it practically, so first, open SQL Server Management Studio, take a new worksheet, and create a new database name, 'DEMOS', shown below:

 

 

Suppose you want to capture all the modifications made to the database index so that you can better monitor the performance of the database server which relates to these index changes.

 

First, create a new table named ‘Index_logs’ to log the index changes.

CREATE TABLE Index_logs (
    log_id INT IDENTITY PRIMARY KEY,
    event_data XML NOT NULL,
    changed_by SYSNAME NOT NULL
);

Also, create one more new table as ‘customers’ which will create the index.

CREATE table customers(
Cust_id INT IDENTITY PRIMARY KEY,
first_name varchar(20),
last_name varchar(20),
address_ varchar(20),
);

Now ‘Index_logs’ table and ‘customers’ are created, when you refresh 'Databases' in object explorer window, you can see it. Alternatively, you can see it by executing the select command.

 

 

Next, create a DDL trigger to track index changes and insert events data into the ‘Index_logs’ table.

CREATE TRIGGER trg_index_changes
ON DATABASE
FOR
    CREATE_INDEX,
    ALTER_INDEX,
    DROP_INDEX
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Index_logs (
        event_data,
        changed_by
    )
    VALUES (
        EVENTDATA(),
        USER
    );
END;

In the body of the trigger, we used the EVENTDATA() function that returns the information about server or database events. The function is only available inside DDL or logon trigger.

 

Then, create indexes for the first_name and lasst_name columns of the customers table:

CREATE INDEX nidx_fname ON customers(first_name);
CREATE INDEX nidx_lname ON customers(last_name);

After that, write execute the below query to check whether the index creation event was captured by the trigger properly.

SELECT * FROM Index_logs;

When you execute the above query it shows the below output:

 

 

If you click on the cell of the 'event_data' column, you can view XML data of the event as follows:

 

 

 

Summary

 

In this article, we learned what about a  trigger, the types of trigger, and how to create a DDL trigger and display the information about an event. In the next article, I will explain a DML trigger and how to create one.